package com.google.code.p.ivans.entities;

import java.io.DataInputStream;
import java.io.DataOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.NotSerializableException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Locale;

import com.google.code.p.ivans.myexceptions.EntityNotFoundException;
import com.google.code.p.ivans.serviceinterfaces.WaiterServiceInterface;

/*
 * �����-��������� ������� �������� ���������;
 * �������(��������) ��� private;
 * ��� �������������� - ����� public ������; 
 */
public class Storage {
	
	public static final int INITIAL_SIZE = 16; 
	
	static String authPath = System.getProperty("user.dir") + "\\files\\authors"; 
	private static Storage storageRef;
	
	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;
		
	static {
		storageRef = new Storage();	
	}		

	public static Storage getInstance() {
		while(storageRef == null) {}
		return storageRef;
	}	
	
	private Storage() {
		createConnection();
	}
	
	public  void createConnection() {
		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
		String login= "SYSTEM";
		String password = "admin";
		try {			
			Locale.setDefault(Locale.ENGLISH);	
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			
			connection = DriverManager.getConnection(url, login,password);
			
			System.out.println("Connection to db created!");
		} catch (SQLException e) {			
			System.out.println("Connection failed");
			System.out.println("e.getMessage():" + e.getMessage());
			System.out.println("e.getSQLState():" + e.getSQLState());
			System.out.println("e.getCause():" + e.getCause());
			System.out.println("e.getErrorCode():" + e.getErrorCode());			
			
			e.printStackTrace();
		} catch (ClassNotFoundException e) {			
			e.printStackTrace();
		}
	}
	
	
	public void addWaiter(Waiter newWaiter) throws SQLException {		
			
		try {		
			statement = connection.createStatement();
			
			// ���������, ���� �� ������� ���������� � ��
			isWaitersTableExist(statement);
					
			newWaiter.getLastName();
			newWaiter.getPatronymicName();		
			
			StringBuilder query = new StringBuilder();
			query.append("insert into WAITERS values (WAITER_ID_SEQUENCE.NEXTVAl, '");
			
			query.append(newWaiter.getFirstName());
			query.append("', '");
			
			query.append(newWaiter.getLastName());
			query.append("', '");
			
			query.append(newWaiter.getPatronymicName());
			query.append("', to_date('");			
			
			String bornDate = gregorianCalendarToString(newWaiter.getBornDate());
			
			query.append(bornDate);
			query.append("', 'yyyy/mm/dd'), to_date('");			
			
			String hireDate = gregorianCalendarToString(newWaiter.getHireDate());
			
			query.append(hireDate);
			query.append("', 'yyyy/mm/dd'), ");
			
			query.append(String.valueOf(newWaiter.getSalary()));	
			query.append(")");	
			
			statement.executeQuery(query.toString());
		}
		finally {
			statement.close();
		}
	}
	
	public void addOrder(Order newOrder) throws SQLException {	
		int idForNewOrder = 0;
		try {
			
			// ���������, ���� �� ������� ������� � ��			
			String isOrdersExist = "SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'ORDERS'";
			
			statement = connection.createStatement();
		
			resultSet = statement.executeQuery(isOrdersExist);
			
			System.out.println(" ��������� ������������� ������� Orders");
			
			// ���� �� ����������, �������
			if(!resultSet.next()) {
				System.out.println(" �� ����������");
				String createOrders = 
						"CREATE TABLE ORDERS" +
							"( ORDER_ID NUMBER PRIMARY KEY," +
							  "NUMBER_OF_TABLE NUMBER(3,0) NOT NULL," +
							  "ORDER_DATE DATE DEFAULT SYSDATE )";
				
				statement.executeQuery(createOrders);		
				System.out.println(" �������");
			}			
			
			// ���������, ���� �� ������� ������ ORDERS_DISHES � ��			
			String isLinksExist = "SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'ORDERS_DISHES'";	
		
			resultSet = statement.executeQuery(isLinksExist);
			
			// ���� �� ����������, �������
			if(!resultSet.next()) {
				String createOrdersDishes = 
						"CREATE TABLE ORDERS_DISHES" +
						"( ORDER_ID NUMBER REFERENCES ORDERS(ORDER_ID)," +
						 " DISH_ID NUMBER REFERENCES DISHES(ID)," +
						 " PRIMARY KEY(ORDER_ID, DISH_ID)" +
						")";
				
				statement.executeQuery(createOrdersDishes);	
			}	
			
			// ��������� ������������� ������������������ ORDERS_SEQ				
			String isOrdersSequenceExist = "SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'ORDERS_SEQ'";
			
			try {				
				resultSet = statement.executeQuery(isOrdersSequenceExist);
					
				// ���� ���, ������� ������������������
				if(!resultSet.next()) {
					
					String createOrdersSeq = "CREATE SEQUENCE ORDERS_SEQ START WITH 1 INCREMENT BY 1";
					
					statement.executeQuery(createOrdersSeq);						
				}
			}
			catch(SQLException e) {
				throw new SQLException(">������ ��� ��������� ������������������ ORDERS_SEQ � ��< " + e.getMessage(), e);
			}			
			
			String insertOrder = "INSERT INTO ORDERS VALUES(ORDERS_SEQ.NEXTVAL, '" +  
					 				newOrder.getNumberOfTable()	+ "', to_date('" +
					 				gregorianCalendarToString(newOrder.getOrderDate()) + "', 'yyyy/mm/dd'))";			
					
			// ��������� ������ � �������
			statement.executeQuery(insertOrder);
			
			connection.commit();
			
			System.out.println(" �������� ����� � �������");
			
			// ��������� ��������� id, ����� ��� ���������� ������ ������_�����			
			resultSet = statement.executeQuery("select ORDERS_SEQ.CURRVAL  FROM DUAL");
			
			System.out.println(" ����� ��� ���� ������������������ �������");
			
			Statement statementForLinks = connection.createStatement();
			try {
				if(resultSet.next()) {
					System.out.println(" ����� � if");
					idForNewOrder = resultSet.getInt(1);
						// ��������� ����� ������_�����
						for(int i = 0; i < newOrder.getDishes().size(); i++) {
							System.out.println(" ����� � for");
							String query = "INSERT INTO ORDERS_DISHES VALUES(" + idForNewOrder + ", " + newOrder.getDishes().get(i) + ")";
							String query1 = "select 1 from dual";
							System.out.println("query:" + query);
							statementForLinks.execute(query);
							System.out.println(" �������� ����� �����_����� � �����");
						}
				}
			} finally {
				statementForLinks.close();
			}
			
		}
		finally {
			statement.close();
		}
	}
	
	public void addlink(int order, int dish) throws SQLException {
		statement = connection.createStatement();
		
		String query = "insert into ORDERS_DISHES values(" + order + ", " + dish + ")";
		
		statement.executeQuery(query);
	}
	
	public void addWaitersOrders(long waiterId, long orderId) throws SQLException {		
		try {
			// ���������, ���� �� ������� ������ WAITERS_ORDERS � ��			
			String isLinksExist = "SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'WAITERS_ORDERS'";	
			
			statement = connection.createStatement();
		
			resultSet = statement.executeQuery(isLinksExist);
			
			// ���� �� ����������, �������
			if(!resultSet.next()) {
				String createWaitersOrders = 
						"CREATE TABLE WAITERS_ORDERS" +
						"( WAITERS_ORDERS_ID NUMBER PRIMARY KEY," +
						 " WAITER_ID NUMBER REFERENCES WAITERS(ID_WAITER) ON DELETE SET NULL," +
						 " ORDER_ID NUMBER REFERENCES ORDERS(ORDER_ID) ON DELETE CASCADE," +
						 " UNIQUE(WAITER_ID, ORDER_ID)" +
						")";
				
				statement.executeQuery(createWaitersOrders);	
			}
			
			// ��������� ������������� ������������������ WAITERS_ORDERS_SEQ				
			String isWaitersOrdersSequenceExist = "SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'WAITERS_ORDERS_SEQ'";
								
			resultSet = statement.executeQuery(isWaitersOrdersSequenceExist);
				
			// ���� ���, ������� ������������������
			if(!resultSet.next()) {
				
				String createOrdersSeq = "CREATE SEQUENCE WAITERS_ORDERS_SEQ START WITH 1 INCREMENT BY 1";
				
				statement.executeQuery(createOrdersSeq);						
			}
			
			// ��������� �����
			statement.executeQuery("INSERT INTO WAITERS_ORDERS VALUES(WAITERS_ORDERS_SEQ.NEXTVAL, " + waiterId + ", " + orderId + ")");
		}
		finally {
			statement.close();
		}
	}
	
	public void removeWaiter(long waiterId) throws SQLException {
		try {
			String query = "delete from WAITERS where ID_WAITER = " + waiterId;
			
			statement = connection.createStatement();	
			
			statement.execute(query);
		}
		finally {
			statement.close();
		}
	}
	
	public void removeOrder(long orderId) throws SQLException {	
		try {
			String query = "DELETE FROM ORDERS WHERE ORDER_ID = " + orderId;
			
			statement = connection.createStatement();	
			
			statement.execute(query);
			
			// ������� ��� ����� ������_�����
			resultSet = statement.executeQuery("DELETE FROM ORDERS_DISHES WHERE ORDER_ID = " + orderId);
		}
		finally {
			statement.close();
		}
		
	}
	
	public Waiter searchWaiterByID(long waiterId) throws EntityNotFoundException, SQLException {	
		Waiter waiter = null;
		try {
			statement = connection.createStatement();
			
			String query = "select ID_WAITER, FIRST_NAME, LAST_NAME, PATRONYMIC_NAME, BORN_DATE, HIRE_DATE, SALARY "
						 + "from WAITERS where ID_WAITER=" + waiterId;
			
			resultSet = statement.executeQuery(query);
			
			if(resultSet.next()) {	
				GregorianCalendar date = stringToGregorianCalendar(resultSet.getString(5));			
		
				waiter = new Waiter(resultSet.getLong(1), 
										   resultSet.getString(2),
										   resultSet.getString(3),
										   resultSet.getString(4), 
										   stringToGregorianCalendar(resultSet.getString(5)),
										   stringToGregorianCalendar(resultSet.getString(6)), 
										   resultSet.getInt(7));								  
				
				return waiter;
			}
			else {
				throw new EntityNotFoundException();
			}
		}
		finally {
			statement.close();
		}
	}
	
	public Order searchOrderByID(long orderId) throws EntityNotFoundException, SQLException {		
		Order order = null;
		try {
			statement = connection.createStatement();
			
			String query = "SELECT ORDER_ID, NUMBER_OF_TABLE, ORDER_DATE FROM ORDERS where ORDER_ID=" + orderId;
			
			resultSet = statement.executeQuery(query);
			
			if(resultSet.next()) {	
				GregorianCalendar date = stringToGregorianCalendar(resultSet.getString(3));	
				long id = resultSet.getLong(1);
				int table = resultSet.getInt(2);
				
				// �������� ��� ������
				resultSet = statement.executeQuery("SELECT DISH_ID FROM ORDERS_DISHES WHERE ORDER_ID = " + orderId);
				
				List<Integer> dishes = new ArrayList<Integer>();
				while(resultSet.next()) {
					dishes.add(resultSet.getInt(1));
				}
		
				order = new Order(id,table, dishes, date);								  
				
				return order;
			}
			else {
				throw new EntityNotFoundException();
			}
		}
		finally {
			statement.close();
			resultSet.close();
		}
	}
	
		
	
	public List<Waiter> getWaiters() throws EntityNotFoundException, SQLException {	
		List<Waiter> waiters = new ArrayList<Waiter>();		
		
		try {
			statement = connection.createStatement();
			
			String query = "select ID_WAITER, FIRST_NAME, LAST_NAME, PATRONYMIC_NAME, BORN_DATE, HIRE_DATE, SALARY from WAITERS";
			
			resultSet = statement.executeQuery(query);
			
			while(resultSet.next()) {							
		
				Waiter waiter = new Waiter(resultSet.getLong(1), 
										   resultSet.getString(2),
										   resultSet.getString(3),
										   resultSet.getString(4), 
										   stringToGregorianCalendar(resultSet.getString(5)),
										   stringToGregorianCalendar(resultSet.getString(6)), 
										   resultSet.getInt(7));								  
				
				waiters.add(waiter);				
			}			
		}
		finally {
			statement.close();
		}
		
		if(waiters.size() == 0)
			throw new EntityNotFoundException();
		
		return waiters;
	}
	
	public List<Order> getOrders() throws EntityNotFoundException, SQLException {		
		List<Order> orders = new ArrayList<Order>();
		
		try {
			statement = connection.createStatement();
			
			String query = "SELECT ORDER_ID, NUMBER_OF_TABLE, ORDER_DATE FROM ORDERS";
			
			resultSet = statement.executeQuery(query);
			
			
			Statement statementForLinks = connection.createStatement();
			
			try {
				while(resultSet.next()) {	
					GregorianCalendar date = stringToGregorianCalendar(resultSet.getString(3));	
					long id = resultSet.getLong(1);
					int table = resultSet.getInt(2);
					
					// �������� ��� ����� �����_�����					
					ResultSet  ordersDishesLinks= statementForLinks.executeQuery("SELECT DISH_ID FROM ORDERS_DISHES WHERE ORDER_ID = " + id);
					
					List<Integer> dishes = new ArrayList<Integer>();
					
					while(ordersDishesLinks.next()) {
						dishes.add(ordersDishesLinks.getInt(1));
					}
			
					Order order = new Order(id,table, dishes, date);								  
					
					orders.add(order);;
				}	
			} finally {
				statementForLinks.close();
			}
		} finally {
			statement.close();
		}
		
		if(orders.size() == 0)
			throw new EntityNotFoundException();
		
		return orders;
	}
	
	
	
	public List<Waiter> getWaitersByName(int searchType, String... params) throws EntityNotFoundException, SQLException {		
		
		List<Waiter> waiters = getWaiters();
				
		List<Waiter> rez = new ArrayList<Waiter>();
		
		// фамилия, имя, отчество
		if(searchType == WaiterServiceInterface.SEARCH_BY_ALL) {
			for(int i = 0; i < waiters.size(); i++) {
				Waiter current = waiters.get(i);				
					if(current.getFirstName().equals(params[0]) && 
						current.getLastName().equals(params[1]) &&
						current.getPatronymicName().equals(params[2])) {
						rez.add(current);	
					}	
			}
		}		
		// имя и фамилия
		else if(searchType == WaiterServiceInterface.SEARCH_BY_FIRSTNAME_AND_LASTNAME) {
			for(int i = 0; i < waiters.size(); i++) {
				Waiter current = waiters.get(i);				
					if(current.getFirstName().equals(params[0]) && 
						current.getLastName().equals(params[1]) ) {
						rez.add(current);	
					}	
			}
		}
		// имя и отчество
		else if(searchType == WaiterServiceInterface.SEARCH_BY_FIRSTNAME_AND_PATRONYMICNAME) {
			for(int i = 0; i < waiters.size(); i++) {
				Waiter current = waiters.get(i);				
					if(current.getFirstName().equals(params[0]) && 
						current.getPatronymicName().equals(params[1]) ) {
						rez.add(current);	
					}		
			}
		}			
		// имя
		else if(searchType == WaiterServiceInterface.SEARCH_BY_FIRSTNAME) {
			for(int i = 0; i < waiters.size(); i++) {
				Waiter current = waiters.get(i);				
					if(current.getFirstName().equals(params[0]) ) {
						rez.add(current);	
					}		
			}
		}			
		// фамилия
		else if(searchType == WaiterServiceInterface.SEARCH_BY_LASTNAME) {
			for(int i = 0; i < waiters.size(); i++) {
				Waiter current = waiters.get(i);				
					if(current.getLastName().equals(params[0]) ) {
						rez.add(current);	
					}		
			}
		}
		// отчество
		else if(searchType == WaiterServiceInterface.SEARCH_BY_PATRONYMICNAME) {
			for(int i = 0; i < waiters.size(); i++) {
				Waiter current = waiters.get(i);				
					if(current.getPatronymicName().equals(params[0]) ) {
						rez.add(current);	
					}		
			}
		}
		if(rez.size() == 0)
			throw new EntityNotFoundException();
		
		return rez;	
	}
	
	public List<Order> OrdersByWaiterId(long id) throws EntityNotFoundException, SQLException {
		
		List<Order> orders = new ArrayList<Order>();
		Statement state = null;
		
		try {
		
			String query = "SELECT ORDERS.ORDER_ID, NUMBER_OF_TABLE, ORDER_DATE FROM ORDERS "
					+ "INNER JOIN WAITERS_ORDERS ON WAITERS_ORDERS.ORDER_ID = ORDERS.ORDER_ID "
					+ "WHERE WAITERS_ORDERS.WAITER_ID = " + id;
			
			state = connection.createStatement();
			
			ResultSet ordersSet = state.executeQuery(query);
			
			while(ordersSet.next()) {
				Order order = searchOrderByID(ordersSet.getLong(1));
				orders.add(order);
			}
		}
		finally {
			state.close();
		}
		
		if(orders.size() == 0)
			throw new EntityNotFoundException();
		
		return orders;
	}
	
	public List<Waiter> WaitersByOrderId(long id) throws EntityNotFoundException, SQLException {

		List<Waiter> waiters = new ArrayList<Waiter>();	
		
		try {		
			String query = "SELECT ID_WAITER, FIRST_NAME, LAST_NAME, PATRONYMIC_NAME, BORN_DATE, HIRE_DATE, SALARY from WAITERS "
					+ "INNER JOIN WAITERS_ORDERS ON WAITERS_ORDERS.WAITER_ID = WAITERS.ID_WAITER "
					+ "WHERE WAITERS_ORDERS.ORDER_ID = " + id;
			
			statement = connection.createStatement();
			
			resultSet = statement.executeQuery(query);
			
			while(resultSet.next()) {
				Waiter waiter = new Waiter(resultSet.getLong(1), 
										   resultSet.getString(2),
										   resultSet.getString(3),
										   resultSet.getString(4), 
										   stringToGregorianCalendar(resultSet.getString(5)),
										   stringToGregorianCalendar(resultSet.getString(6)), 
										   resultSet.getInt(7));
				
				waiters.add(waiter);
			}
		}
		finally {
			statement.close();
		}
		
		if(waiters.size() == 0)
			throw new EntityNotFoundException();
		
		return waiters;
	}

	public void updateWaiter(long waiterId, int field, Object value) throws EntityNotFoundException, SQLException {
			
		Waiter waiter = null;
		try {
			statement = connection.createStatement();
			
			String query = "select ID_WAITER, FIRST_NAME, LAST_NAME, PATRONYMIC_NAME, BORN_DATE, HIRE_DATE, SALARY "
						 + "from WAITERS where ID_WAITER=" + waiterId;
			
			resultSet = statement.executeQuery(query);
			GregorianCalendar oldDate;
			String newDate;
			if(resultSet.next()) {				
				switch(field) {
				case Waiter.FIRSTNAME_UPDATE:							
					query = "update WAITERS set FIRST_NAME='" + (String)value + "' where ID_WAITER=" + waiterId;
					statement.executeQuery(query);
					break;
				case Waiter.LASTNAME_UPDATE: 		
					query = "update WAITERS set LAST_NAME='" + (String)value + "' where ID_WAITER=" + waiterId;
					statement.executeQuery(query);
					break;							
				case Waiter.PATRONYMICNAME_UPDATE:							
					query = "update WAITERS set PATRONYMIC_NAME='" + (String)value + "' where ID_WAITER=" + waiterId;
					statement.executeQuery(query);
					break;							
				case Waiter.BORN_DAY_UPDATE: 							
					oldDate = stringToGregorianCalendar(resultSet.getString(5));
					oldDate.set(oldDate.get(Calendar.YEAR), oldDate.get(Calendar.MONTH), (Integer)value);
					newDate = gregorianCalendarToString(oldDate);					
					query = "update WAITERS set BORN_DATE=to_date('" + newDate + "','yyyy/mm/dd') where ID_WAITER=" + waiterId;
					statement.executeQuery(query);		
					break;							
				case Waiter.BORN_MONTH_UPDATE: 							
					oldDate = stringToGregorianCalendar(resultSet.getString(5));
					oldDate.set(oldDate.get(Calendar.YEAR), ((Integer)value - 1), oldDate.get(Calendar.DAY_OF_MONTH));
					newDate = gregorianCalendarToString(oldDate);					
					query = "update WAITERS set BORN_DATE=to_date('" + newDate + "','yyyy/mm/dd') where ID_WAITER=" + waiterId;
					statement.executeQuery(query);		
					break;	
				case Waiter.BORN_YEAR_UPDATE:							
					oldDate = stringToGregorianCalendar(resultSet.getString(5));
					oldDate.set((Integer)value, oldDate.get(Calendar.MONTH), oldDate.get(Calendar.DAY_OF_MONTH));
					newDate = gregorianCalendarToString(oldDate);					
					query = "update WAITERS set BORN_DATE=to_date('" + newDate + "','yyyy/mm/dd') where ID_WAITER=" + waiterId;
					statement.executeQuery(query);	
					break;
				case Waiter.HIRE_DAY_UPDATE: 							
					oldDate = stringToGregorianCalendar(resultSet.getString(6));
					oldDate.set(oldDate.get(Calendar.YEAR), oldDate.get(Calendar.MONTH), (Integer)value);
					newDate = gregorianCalendarToString(oldDate);					
					query = "update WAITERS set HIRE_DATE=to_date('" + newDate + "','yyyy/mm/dd') where ID_WAITER=" + waiterId;
					statement.executeQuery(query);		
					break;							
				case Waiter.HIRE_MONTH_UPDATE: 							
					oldDate = stringToGregorianCalendar(resultSet.getString(6));
					oldDate.set(oldDate.get(Calendar.YEAR), ((Integer)value - 1), oldDate.get(Calendar.DAY_OF_MONTH));
					newDate = gregorianCalendarToString(oldDate);					
					query = "update WAITERS set HIRE_DATE=to_date('" + newDate + "','yyyy/mm/dd') where ID_WAITER=" + waiterId;
					statement.executeQuery(query);	
					break;							
				case Waiter.HIRE_YEAR_UPDATE:							
					oldDate = stringToGregorianCalendar(resultSet.getString(6));
					oldDate.set((Integer)value, oldDate.get(Calendar.MONTH), oldDate.get(Calendar.DAY_OF_MONTH));
					newDate = gregorianCalendarToString(oldDate);					
					query = "update WAITERS set HIRE_DATE=to_date('" + newDate + "','yyyy/mm/dd') where ID_WAITER=" + waiterId;
					statement.executeQuery(query);
					break;
				case Waiter.SALARY_UPDATE: 
					query = "update WAITERS set SALARY=" + (Integer)value + " where ID_WAITER=" + waiterId;
					statement.executeQuery(query);
					break;						
				default: 
					throw new IllegalArgumentException();
				}
			}				
		}
		finally {
			statement.close();
		}		
	}
	
	public GregorianCalendar stringToGregorianCalendar(String str) {
		int year = Integer.valueOf(str.substring(0,4));
		int month = Integer.valueOf(str.substring(5,7)) - 1;
		int day = Integer.valueOf(str.substring(8,10));
		
		return new GregorianCalendar(year, month, day);
	}
	
	public String gregorianCalendarToString(GregorianCalendar date) {
		// ���� ��������, ������ YYYY/MM/DD
		StringBuilder builder = new StringBuilder();
		
		builder.append(String.valueOf(date.get(Calendar.YEAR)));
		builder.append("/");
		
		int month = date.get(Calendar.MONTH) + 1;			
		if(month < 10)
			builder.append("0");
		
		builder.append(String.valueOf(month));
		builder.append("/");
		
		int day = date.get(Calendar.DAY_OF_MONTH);			
		if(day < 10)
			builder.append("0");
		
		builder.append(String.valueOf(day));
		
		return builder.toString();
	}
	
	public void isWaitersTableExist(Statement statement) throws SQLException {
		
		String isWaitersExist = "SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'WAITERS'";
	
		resultSet = statement.executeQuery(isWaitersExist);
		
		// ���� �� ����������, �������
		if(!resultSet.next()) {
			String createWaiters = 
					"CREATE TABLE WAITERS " + 
					"( ID_WAITER NUMBER(6,0) PRIMARY KEY," +
					"  FIRST_NAME VARCHAR2(100 CHAR) NOT NULL," + 
					"  LAST_NAME VARCHAR2(100 CHAR) NOT NULL," + 
					"  PATRONYMIC_NAME VARCHAR2(100 CHAR)," + 
					"  BORN_DATE DATE NOT NULL," +
					"  HIRE_DATE DATE NOT NULL," + 
					"  SALARY NUMBER(10,2) NOT NULL)";
			
			statement.executeQuery(createWaiters);
		}
	}
	
	public void isOrdersTableExist(Statement statement) throws SQLException {
		
		String isOrdersExist = "SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'WAITERS'";
	
		resultSet = statement.executeQuery(isOrdersExist);
		
		// ���� �� ����������, �������
		if(!resultSet.next()) {
			String createWaiters = 
					"CREATE TABLE WAITERS " + 
					"( ID_WAITER NUMBER(6,0) PRIMARY KEY," +
					"  FIRST_NAME VARCHAR2(100 CHAR) NOT NULL," + 
					"  LAST_NAME VARCHAR2(100 CHAR) NOT NULL," + 
					"  PATRONYMIC_NAME VARCHAR2(100 CHAR)," + 
					"  BORN_DATE DATE NOT NULL," +
					"  HIRE_DATE DATE NOT NULL," + 
					"  SALARY NUMBER(10,2) NOT NULL)";
			
			statement.executeQuery(createWaiters);
		}
	}

}
